#!/usr/bin/env bash
source /etc/profile

if [ -n "$1" ]
then
  do_date=$1
else
  do_date=$(date -d "-1 day" +%F)
fi

sql="
insert overwrite table ads.ads_member_retention_count
partition (dt='$do_date')
select add_date,
  retention_date,
  count(*) retention_count
from dws.dws_member_retention_day
where dt='$do_date'
group by add_date, retention_date;

insert overwrite table ads.ads_member_retention_rate
partition(dt='$do_date')
select t1.add_date,
  t1.retention_day,
  t1.retention_count,
  t2.cnt,
  t1.retention_count / t2.cnt * 100
from ads.ads_member_retention_count t1
  join ads.ads_new_member_cnt t2
    on t1.dt=t2.dt
where t1.dt='$do_date';
"

hive -e "$sql"